﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace FYPDemo
{
    public class Reward_Module
    {
        public static DataTable listRewardProduct(string prodName, int pointAbove, int pointBelow, int QuantityBelow, int status)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            conndb db = new conndb();

            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listRewardProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProdName", SqlDbType.VarChar, 50));
                cmd.Parameters["@ProdName"].Value = prodName;

                cmd.Parameters.Add(new SqlParameter("@PointAbove", SqlDbType.Int));
                cmd.Parameters["@PointAbove"].Value = pointAbove;

                cmd.Parameters.Add(new SqlParameter("@PointBelow", SqlDbType.Int));
                cmd.Parameters["@PointBelow"].Value = pointBelow;

                cmd.Parameters.Add(new SqlParameter("@QuantityBelow", SqlDbType.Int));
                cmd.Parameters["@QuantityBelow"].Value = QuantityBelow;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = status;

                da.SelectCommand = cmd;
                da.Fill(dt);

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable showRewardProduct(int ID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "showRewardProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static int getRewardPoint(string name)
        {
            int result = 0;

            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "checkRewardPoint";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                da.SelectCommand = cmd;
                da.Fill(dt);

                result = Convert.ToInt32(dt.Rows[0][0]);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

        public static int updateRewardPoint(string name, int point)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "updateRewardPoint";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@Point", SqlDbType.Int));
                cmd.Parameters["@Point"].Value = point;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

        public static int insertRewardFlow(string name, int totalpoint, string type, int prodID, int point)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "insertRedeemFlow";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@TotalPoint", SqlDbType.Int));
                cmd.Parameters["@TotalPoint"].Value = totalpoint;

                cmd.Parameters.Add(new SqlParameter("@Type", SqlDbType.VarChar, 50));
                cmd.Parameters["@Type"].Value = type;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.Int));
                cmd.Parameters["@ProductID"].Value = prodID;

                cmd.Parameters.Add(new SqlParameter("@ProductPoint", SqlDbType.Int));
                cmd.Parameters["@ProductPoint"].Value = point;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

    }
}